﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;

namespace OpenXML.DAL
{
    public class CountryHandler
    {
        public string getCountrybyCode(string code)
        {
            string countryName = null;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

            SqlCommand cmd = new SqlCommand("Select CountryName from Country Where CountryCode = '" + code + "';", conn);
            SqlDataReader reader;

            try
            {
                conn.Open();
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    ListItem item = new ListItem();
                    item.Text = reader["CountryName"].ToString();
                    item.Value = reader["CountryName"].ToString();
                    countryName = item.Value;
                }
                reader.Close();
            }
            catch (Exception err)
            {
                Console.Write(err.Message);
            }
            finally
            {
                conn.Close();
            }

            return countryName;
        }

        public string getCountryCode(string name)
        {
            string countryCode = null;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = conn.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

            SqlCommand cmd = new SqlCommand("Select CountryCode from Country Where CountryName = '" + name + "';", conn);
            SqlDataReader reader;

            try
            {
                conn.Open();
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    ListItem item = new ListItem();
                    item.Text = reader["CountryCode"].ToString();
                    item.Value = reader["CountryCode"].ToString();
                    countryCode = item.Value;
                }
                reader.Close();
            }
            catch (Exception err)
            {
                Console.Write(err.Message);
            }
            finally
            {
                conn.Close();
            }

            return countryCode;
        }

        public List<string> getListCountryName()
        {
            List<string> li = new List<string>();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = conn.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

            SqlCommand cmd = new SqlCommand("Select CountryName from Country;", conn);
            SqlDataReader reader;

            try
            {
                conn.Open();
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    li.Add(reader["CountryName"].ToString());
                }
            }
            catch (Exception err)
            {
                Console.Write(err.Message);
            }
            finally
            {
                conn.Close();
            }

            return li;
        }

        public List<string> getListCountryNameOrdered()
        {
            List<string> li = new List<string>();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = conn.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

            SqlCommand cmd = new SqlCommand("Select CountryName from Country Order by CountryName ASC;", conn);
            SqlDataReader reader;

            try
            {
                conn.Open();
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    li.Add(reader["CountryName"].ToString());
                }
            }
            catch (Exception err)
            {
                Console.Write(err.Message);
            }
            finally
            {
                conn.Close();
            }

            return li;
        }

        public List<string> getListCountryCode()
        {
            List<string> li = new List<string>();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = conn.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

            SqlCommand cmd = new SqlCommand("Select CountryCode from Country;", conn);
            SqlDataReader reader;

            try
            {
                conn.Open();
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    li.Add(reader["CountryCode"].ToString());
                }
            }
            catch (Exception err)
            {
                Console.Write(err.Message);
            }
            finally
            {
                conn.Close();
            }

            return li;
        }

        public List<string> getListCountryCodeOrdered()
        {
            List<string> li = new List<string>();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = conn.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

            SqlCommand cmd = new SqlCommand("Select CountryCode from Country Order by CountryName Asc;", conn);
            SqlDataReader reader;

            try
            {
                conn.Open();
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    li.Add(reader["CountryCode"].ToString());
                }
            }
            catch (Exception err)
            {
                Console.Write(err.Message);
            }
            finally
            {
                conn.Close();
            }

            return li;
        }

        public List<ListItem> getListCountries()
        {
            List<ListItem> lCountries = new List<ListItem>();

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = conn.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

            SqlCommand cmd = new SqlCommand("Select * from Country;", conn);
            SqlDataReader reader;

            try
            {
                conn.Open();
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    ListItem item = new ListItem();
                    item.Value = reader["CountryCode"].ToString();
                    item.Text = reader["CountryName"].ToString();
                    lCountries.Add(item);
                }
            }
            catch (Exception err)
            {
                Console.Write(err.Message);
            }
            finally
            {
                conn.Close();
            }

            return lCountries;
        }

        public List<ListItem> getListCountriesOrdered()
        {
            List<ListItem> lCountries = new List<ListItem>();

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = conn.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

            SqlCommand cmd = new SqlCommand("Select * from Country Order by CountryName Asc;", conn);
            SqlDataReader reader;

            try
            {
                conn.Open();
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    ListItem item = new ListItem();
                    item.Value = reader["CountryCode"].ToString();
                    item.Text = reader["CountryName"].ToString();
                    lCountries.Add(item);
                }
            }
            catch (Exception err)
            {
                Console.Write(err.Message);
            }
            finally
            {
                conn.Close();
            }

            return lCountries;
        }

        public List<ListItem> getListCountriesOrderedByCode()
        {
            List<ListItem> lCountries = new List<ListItem>();

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = conn.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

            SqlCommand cmd = new SqlCommand("Select * from Country Order by CountryCode Asc;", conn);
            SqlDataReader reader;

            try
            {
                conn.Open();
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    ListItem item = new ListItem();
                    item.Value = reader["CountryCode"].ToString();
                    item.Text = reader["CountryName"].ToString();
                    lCountries.Add(item);
                }
            }
            catch (Exception err)
            {
                Console.Write(err.Message);
            }
            finally
            {
                conn.Close();
            }

            return lCountries;
        }
    }
}